﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess
{
    public class ControllerSolicitud
    {

        #region ATRIBUTOS

        private SqlConnection conexion = DBConnection.getInstancia().getConnection();
        private static ControllerSolicitud unConSol;

        #endregion

        #region SINGLETON

        public static ControllerSolicitud getInstancia()
        {
            if (unConSol == null)
            {
                unConSol = new ControllerSolicitud();
            }
            return unConSol;
        }

        #endregion

        #region CONSTRUCTORES

        private ControllerSolicitud()
        { }

        #endregion

        #region METODOS
        //Insertar una nueva solicitud.
        public void insertSolicitud(string pAsunto, string pDescrip, Int32 pNroUsu)
        {
            try
            {
                //Sentencia SQL
                string sql = "INSERT INTO solicitudes(solasunto, soldescripcion, solestado, solnrousuario, solnrotecnico, solcreada, solcerrada) values (@solasunto, @soldescripcion, default, @solnrousuario, default, default, default);";
                //Se crea el comando.
                SqlCommand comm = new SqlCommand(sql, conexion);
                comm.Parameters.AddWithValue("solasunto", truncar(pAsunto,99));
                comm.Parameters.AddWithValue("soldescripcion", truncar(pDescrip, 499));
                comm.Parameters.AddWithValue("solnrousuario", pNroUsu);

                //Se abre la conexion.
                conexion.Open();
                //Se ejecuta el comando.
                int x = comm.ExecuteNonQuery();
                
                //Si ninguna fila fue modificada, se tira una excepcion.
                if (x == 0)
                {
                    throw new Exception("Error al insertar una nueva solicitud");
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Error al insertar una nueva solicitud: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
        }

        //Asignar un tecnico a una solicitud.
        public void asignarTecnico(Int32 pTecNro, Int32 pNroSol)
        {
            try
            {
                //Sentencia SQL
                string sql = "UPDATE solicitudes SET solnrotecnico = @solnrotecnico WHERE solnroticket = @solnroticket;";
                //Se crea el comando.
                SqlCommand comm = new SqlCommand(sql, conexion);
                comm.Parameters.AddWithValue("solnrotecnico", pTecNro);
                comm.Parameters.AddWithValue("solnroticket", pNroSol);

                //Se abre la conexion.
                conexion.Open();
                //Se ejecuta el comando.
                int x = comm.ExecuteNonQuery();

                //Si ninguna fila fue modificada, se tira una excepcion.
                if (x == 0)
                {
                    throw new Exception("Error al asignar un tecnico a una solicitud");
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Error al asignar un tecnico a una solicitud: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
        }

        //Cambiar el estado a una solicitud.
        public void CambiarEstado(string pEstado, Int32 pNroSol)
        {
            //Sentencia SQL
            string sql = "";
            if(pEstado == "Abierto")
            {
                //Si se reabre una solicitud, se le setea la fecha de cerrada a null y se le cambia el estado a 'Abierto'.
                sql = "UPDATE solicitudes SET solestado = 'Abierto', solcerrada = NULL WHERE solnroticket = @solnroticket;";
            }
            else if(pEstado == "Cerrado")
            {
                //Si se cierra una solicitud se le cambia el estado a 'Cerrado' y se le asigna como fecha de cerrada el retorno de
                //la funcion GETDATE().
                sql = "UPDATE solicitudes SET solestado = 'Cerrado', solcerrada = getdate() WHERE solnroticket = @solnroticket;";
            }
            else if(pEstado == "Cancelado")
            {
                //Si se cancela una solicitud se le cambia el estado a 'Cancelado' y se le asigna como fecha de cancelada el retorno de
                //la funcion GETDATE().
                sql = "UPDATE solicitudes SET solestado = 'Cancelado', solcerrada = getdate() WHERE solnroticket = @solnroticket;";
            }
            try
            {
                //Se crea el comando.
                SqlCommand comm = new SqlCommand(sql, conexion);
                comm.Parameters.AddWithValue("solnroticket", pNroSol);

                //Se abre la conexion.
                conexion.Open();
                //Se ejecuta el comando.
                int x = comm.ExecuteNonQuery();

                //Si ninguna fila fue modificada, se tira una excepcion.
                if (x == 0)
                {
                    throw new Exception("Error al cambiar el estado a una solicitud");
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Error al cambiar el estado a una solicitud: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
        }

        //Dar solicitud por numero de ticket.
        public DataTable getSolicitudByNro(Int32 pNro)
        {
            //Consulta.
            string sql = "SELECT * FROM solicitudes WHERE solnroticket = @solnroticket;";
            //Se crea el datatable.
            DataTable myDt = new DataTable();
            //Se crea el comando.
            SqlCommand myComm = new SqlCommand(sql, conexion);
            myComm.Parameters.AddWithValue("solnroticket", pNro);
            //Se crea el data adapter para llenar la tabla y se le pasa el comando como parametro.
            SqlDataAdapter myDa = new SqlDataAdapter(myComm);
            try
            {
                //Se abre la conexion.
                conexion.Open();
                //Se llena la tabla con el data adapter.
                myDa.Fill(myDt);
            }
            catch (Exception ex)
            {
                throw new Exception("Error al buscar solicitud por numero: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
            //Se devuelve el data table.
            return myDt;
        }

        //Dar solicitudes por asignacion de Tecnico (asignado o no asignado).
        public DataTable getSolicitudesByAsignacion(bool pAsignado)
        {
            string sql = "";
            //Si se piden las solicitudes que tienen un tecnico asignado...
            if (pAsignado == true)
            {
                //Consulta.
                sql = "SELECT x.solnroticket AS Ticket,  SUBSTRING(x.solasunto,1,26) AS Asunto, CONCAT(SUBSTRING(x.soldescripcion,1,35), '...') AS Descripcion, x.solcreada AS Fecha, x.nombreCompleto AS Usuario, CONCAT(t.pernombre,' ',t.perapellido) AS Tecnico FROM (SELECT s.solnroticket, s.solasunto, s.soldescripcion, s.solcreada, s.solnrotecnico, CONCAT(p.pernombre,' ',p.perapellido) AS nombreCompleto FROM solicitudes AS s INNER JOIN personas AS p ON s.solnrousuario = p.pernrofuncionario WHERE s.solnrotecnico IS NOT NULL AND s.solestado = 'Abierto') AS x INNER JOIN personas AS t ON x.solnrotecnico = t.pernrofuncionario ORDER BY x.solcreada DESC";
            }
            else
            {
                //Si se piden las solicitudes que NO tienen un tecnico asignado.
                sql = "SELECT s.solnroticket AS Ticket, SUBSTRING(s.solasunto,1,26) AS Asunto, CONCAT(SUBSTRING(s.soldescripcion,1,35), '...') AS Descripcion, s.solcreada AS Fecha, CONCAT(p.pernombre,' ',p.perapellido) AS Usuario FROM solicitudes AS s INNER JOIN personas AS p ON s.solnrousuario = p.pernrofuncionario WHERE s.solnrotecnico IS NULL AND s.solestado = 'Abierto' ORDER BY s.solcreada DESC";
            }
            //Se crea el datatable.
            DataTable myDt = new DataTable();
            //Se crea el comando.
            SqlCommand myComm = new SqlCommand(sql, conexion);
            //Se crea el data adapter para llenar la tabla y se le pasa el comando como parametro.
            SqlDataAdapter myDa = new SqlDataAdapter(myComm);
            try
            {
                //Se abre la conexion.
                conexion.Open();
                //Se llena la tabla con el data adapter.
                myDa.Fill(myDt);
            }
            catch (Exception ex)
            {
                throw new Exception("Error al buscar solicitudes por asignacion: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
            //Se devuelve el data table.
            return myDt;
        }
        //Dar solicitudES por numero de Tecnico.
        public DataTable getSolicitudesByNroTecnico(Int32 pNro)
        {
            //Consulta.
            string sql = "SELECT s.solnroticket AS Ticket, SUBSTRING(s.solasunto,1,25) AS Asunto, CONCAT(SUBSTRING(s.soldescripcion,1,35), '...') AS Descripcion, s.solcreada AS Fecha, CONCAT(p.pernombre,' ',p.perapellido) AS Usuario FROM solicitudes AS s INNER JOIN personas AS p ON s.solnrousuario = p.pernrofuncionario WHERE s.solnrotecnico = @tecnico AND s.solestado = 'Abierto' ORDER BY s.solcreada DESC";
            //Se crea el datatable.
            DataTable myDt = new DataTable();
            //Se crea el comando.
            SqlCommand myComm = new SqlCommand(sql, conexion);
            myComm.Parameters.AddWithValue("tecnico", pNro);
            //Se crea el data adapter para llenar la tabla y se le pasa el comando como parametro.
            SqlDataAdapter myDa = new SqlDataAdapter(myComm);
            try
            {
                //Se abre la conexion.
                conexion.Open();
                //Se llena la tabla con el data adapter.
                myDa.Fill(myDt);
            }
            catch (Exception ex)
            {
                throw new Exception("Error al buscar solicitud por numero: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
            //Se devuelve el data table.
            return myDt;
        }

        //Dar solicitudES por numero de Usuario y estado.
        public DataTable getSolByNroUsuAndEstado(Int32 pNro, string pEstado)
        {
            //Consulta.
            string sql = "";
            if(pEstado == "Abierto")
            {
                sql = "SELECT s.solnroticket AS Ticket, SUBSTRING(s.solasunto,1,25) AS Asunto, CONCAT(SUBSTRING(s.soldescripcion,1,35), '...') AS Descripcion, s.solcreada AS [Fecha creada], (CASE WHEN s.solnrotecnico IS NULL  THEN 'Ninguno' ELSE (CONCAT(p.pernombre,' ',p.perapellido)) END) AS [Tecnico Asignado] FROM solicitudes AS s LEFT JOIN personas AS p ON s.solnrotecnico = p.pernrofuncionario WHERE s.solnrousuario = @usuario AND s.solestado = 'Abierto' ORDER BY s.solcreada DESC";
            }
            else
            {
                sql = "SELECT s.solnroticket AS Ticket, SUBSTRING(s.solasunto,1,25) AS Asunto, CONCAT(SUBSTRING(s.soldescripcion,1,35), '...') AS Descripcion, s.solcreada AS [Fecha creada], s.solcerrada AS [Fecha cerrada], (CASE WHEN s.solnrotecnico IS NULL  THEN 'Ninguno' ELSE (CONCAT(p.pernombre,' ',p.perapellido)) END) AS [Tecnico Asignado] FROM solicitudes AS s LEFT JOIN personas AS p ON s.solnrotecnico = p.pernrofuncionario WHERE s.solnrousuario = @usuario AND (s.solestado = 'Cancelado' OR s.solestado = 'Cerrado') ORDER BY s.solcreada DESC";
            }
            //Se crea el datatable.
            DataTable myDt = new DataTable();
            //Se crea el comando.
            SqlCommand myComm = new SqlCommand(sql, conexion);
            myComm.Parameters.AddWithValue("usuario", pNro);
            //Se crea el data adapter para llenar la tabla y se le pasa el comando como parametro.
            SqlDataAdapter myDa = new SqlDataAdapter(myComm);
            try
            {
                //Se abre la conexion.
                conexion.Open();
                //Se llena la tabla con el data adapter.
                myDa.Fill(myDt);
            }
            catch (Exception ex)
            {
                throw new Exception("Error al buscar solicitud por usuario y estado de solicitud: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
            //Se devuelve el data table.
            return myDt;
        }

        //Dar solicitudes asignadas por nro de tecnico.
        public DataTable getSolicitudesByTecAsig(Int32 pNro)
        {
            string sql = "SELECT x.solnroticket AS Ticket,  SUBSTRING(x.solasunto,1,26) AS Asunto, CONCAT(SUBSTRING(x.soldescripcion,1,35), '...') AS Descripcion, x.solcreada AS Fecha, x.nombreCompleto AS Usuario, CONCAT(t.pernombre,' ',t.perapellido) AS Tecnico FROM (SELECT s.solnroticket, s.solasunto, s.soldescripcion, s.solcreada, s.solnrotecnico, CONCAT(p.pernombre,' ',p.perapellido) AS nombreCompleto FROM solicitudes AS s INNER JOIN personas AS p ON s.solnrousuario = p.pernrofuncionario WHERE s.solnrotecnico= @pNro AND s.solestado = 'Abierto') AS x INNER JOIN personas AS t ON x.solnrotecnico = t.pernrofuncionario ORDER BY x.solcreada DESC";
            
            //Se crea el datatable.
            DataTable myDt = new DataTable();
            //Se crea el comando.
            SqlCommand myComm = new SqlCommand(sql, conexion);
            myComm.Parameters.AddWithValue("pNro", pNro);
            //Se crea el data adapter para llenar la tabla y se le pasa el comando como parametro.
            SqlDataAdapter myDa = new SqlDataAdapter(myComm);
            try
            {
                //Se abre la conexion.
                conexion.Open();
                //Se llena la tabla con el data adapter.
                myDa.Fill(myDt);
            }
            catch (Exception ex)
            {
                throw new Exception("Error al buscar solicitudes por asignacion y numero de tecnico: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
            //Se devuelve el data table.
            return myDt;
        }

        string truncar(string unSt, int maxLength)
        {
            return unSt.Length <= maxLength ? unSt : unSt.Substring(0, maxLength);
        } 

        //Dar lista de solicitudes ingresadas y resueltas por mes para el año corriente.
        public List<string> getListSolByMesAndAnio()
        {
            string sql = "(select 'Enero' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 1) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 1) as b)union(select 'Febrero' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 2) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 2) as b)union(select 'Marzo' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 3) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 3) as b)union(select 'Abril' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 4) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 4) as b)union(select 'Mayo' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 5) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 5) as b)union(select 'Junio' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 6) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 6) as b)union(select 'Julio' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 7) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 7) as b)union(select 'Agosto' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 8) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 8) as b)union(select 'Setiembre' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 9) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 9) as b)union(select 'Octubre' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 10) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 10) as b)union(select 'Noviembre' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 11) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 11) as b)union(select 'Diciembre' as Mes, a.cant as Ingresadas, b.cant as Resueltas from (select count(*) as cant from solicitudes where year(solcreada) = year(getdate()) and month(solcreada) = 12) as a, (select count(*) as cant from solicitudes where year(solcerrada) = year(getdate()) and month(solcerrada) = 12) as b);";

            //Se crea el data reader.
            SqlDataReader mydr;
            //Se crea el comando.
            SqlCommand myComm = new SqlCommand(sql, conexion);
            //Se crea la lista de retorno.
            List<string> lstSol = new List<string>();
            try
            {
                //Se abre la conexion.
                conexion.Open();
                //Se ejecuta un Reader desde el comando creado y se le asigna al DataReader.
                mydr = myComm.ExecuteReader();
                while (mydr.Read())
                {
                    //...agregamos el nombre del cargo a la lista.
                    lstSol.Add("Mes: " + Convert.ToString(mydr["Mes"]) + " " + Convert.ToString(mydr["Ingresadas"]) + " Ingresadas, " + Convert.ToString(mydr["Resueltas"]) + " Resueltas");
                }
                //Se cierra el DataReader.
                mydr.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Error al buscar solicitudes: " + ex.Message);
            }
            finally
            {
                //Se cierra la conexion.
                conexion.Close();
            }
            //Se devuelve el data table.
            return lstSol;
        }
        #endregion
    }
}
